﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.ComponentModel;


namespace outREACH.App_Code
{

    public class Users
    {

        public string userName { get; set; }
        public string password { get; set; }
        public string firstName { get; set; }
        public string lastName { get; set; }
        public DateTime dob { get; set; }
        public string gender { get; set; }
        public string hp { get; set; }
        public string home { get; set; }
        public string email { get; set; }
        public string country { get; set; }
        public string city { get; set; }
        public string address { get; set; }
        public string role { get; set; }
        public string zipCode { get; set; }
        public string imagePath { get; set; }
        public string imageName { get; set; }
        public DateTime dateJoin { get; set; }

        public static List<Users> GetUser()
        {
            SqlConnection conn = null;
            List<Users> result = new List<Users>();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Users";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Users u = new Users();
                    u.address = (string)dr["address"];
                    u.city = (string)dr["city"];
                    u.country = (string)dr["country"];
                    u.dob = (DateTime)dr["DOB"];
                    u.email = (string)dr["email"];
                    u.firstName = (string)dr["firstName"];
                    u.gender = (string)dr["gender"];
                    u.home = (string)dr["homeNumber"];
                    u.hp = (string)dr["handphone"];
                    u.lastName = (string)dr["lastName"];
                    u.password = (string)dr["password"];
                    u.userName = (string)dr["userName"];
                    u.role = (string)dr["role"];
                    u.zipCode = (string)dr["zipCode"];
                    u.imageName = (string)dr["imageName"];
                    u.imagePath= (string)dr["imagePath"];
                    u.dateJoin = (DateTime)dr["dateJoin"];
                    
                    result.Add(u);
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static Users AuthorizeUser(string userName)
        {
            SqlConnection conn = null;
            Users u = new Users();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Users where userName = @userName";
                comm.Parameters.AddWithValue("@userName", userName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    u.password = (string)dr["password"];
                    u.userName = (string)dr["userName"];
                    u.role = (string)dr["role"];
                    u.country = (string)dr["country"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return u;
        }

        public static Users GetName(string userName)
        {
            SqlConnection conn = null;
            Users u = new Users();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Users where userName = @userName";
                comm.Parameters.AddWithValue("@userName", userName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    u.firstName = (string)dr["firstName"];
                    u.userName = (string)dr["userName"];
                    u.lastName = (string)dr["lastName"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return u;
        }

        public static void Insert(Users u)
        {
            SqlConnection conn = null;
            try
            {
                SqlCommand comm = new SqlCommand();
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Users(userName,password,firstName,lastName,DOB,gender,handphone,homeNumber,email,country,city,address,zipCode,role,imagePath,imageName,dateJoin)VALUES(@userName,@password,@firstName,@lastName,@DOB,@gender,@handphone,@homeNumber,@email,@country,@city,@address,@zipCode,@role,@imagePath,@imageName,@dateJoin)";
                comm.Parameters.AddWithValue("@userName", u.userName);
                comm.Parameters.AddWithValue("@password", u.password);
                comm.Parameters.AddWithValue("@firstName", u.firstName);
                comm.Parameters.AddWithValue("@lastName", u.lastName);
                comm.Parameters.AddWithValue("@DOB", u.dob);
                comm.Parameters.AddWithValue("@gender", u.gender);
                comm.Parameters.AddWithValue("@handphone", u.hp);
                comm.Parameters.AddWithValue("@homeNumber", u.home);
                comm.Parameters.AddWithValue("@email", u.email);
                comm.Parameters.AddWithValue("@country", u.country);
                comm.Parameters.AddWithValue("@city", u.city);
                comm.Parameters.AddWithValue("@address", u.address);
                comm.Parameters.AddWithValue("@zipCode", u.zipCode);
                comm.Parameters.AddWithValue("@role", u.role);
                comm.Parameters.AddWithValue("@imagePath", u.imagePath);
                comm.Parameters.AddWithValue("@imageName", u.imageName);
                comm.Parameters.AddWithValue("@dateJoin", u.dateJoin);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void changePassword(string userName, string password, string newPass)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "update Users set password = @newPass where userName = @userName and password = @password";
                comm.Parameters.AddWithValue("@userName", userName);
                comm.Parameters.AddWithValue("@password", password);
                comm.Parameters.AddWithValue("@newPass", newPass);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static Users GetCountry(string userName)
        {
            SqlConnection conn = null;
            Users u = new Users();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Users where userName = @userName";
                comm.Parameters.AddWithValue("@userName", userName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    u.country = (string)dr["country"];
                    u.userName = (string)dr["userName"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return u;
        }

        public static void changeAvatars(string imagePath, string imageName, string newImagePath, string newImageName)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "update Users set imagePath = @newImagePath, imageName = @newImageName where imagePath = @imagePath and imageName = @imageName";
                comm.Parameters.AddWithValue("@imagePath", imagePath);
                comm.Parameters.AddWithValue("@imageName", imageName);
                comm.Parameters.AddWithValue("@newImagePath", newImagePath);
                comm.Parameters.AddWithValue("@newImageName", newImageName);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void changeEmail(string userName, string newEmail)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "update Users set email = @newEmail where userName = @userName";
                comm.Parameters.AddWithValue("@newEmail", newEmail);
                comm.Parameters.AddWithValue("@userName", userName);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static Users getUserProfile(string userName)
        {
            SqlConnection conn = null;
            Users u = new Users();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Users where userName = @userName";
                comm.Parameters.AddWithValue("@userName", userName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    
                    u.country = (string)dr["country"];
                    u.userName = (string)dr["userName"];
                    u.firstName = (string)dr["firstName"];
                    u.lastName = (string)dr["lastName"];
                    u.city = (string)dr["city"];
                    u.imageName = (string)dr["imageName"];
                    u.imagePath = (string)dr["imagePath"];
                    
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return u;
        }

        public static Users GetEmailDetails(string userName)
        {
            SqlConnection conn = null;
            Users u = new Users();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select email, userName, password from Users where userName = @userName";
                comm.Parameters.AddWithValue("@userName", userName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    u.email = (string)dr["email"];
                    u.userName = (string)dr["userName"];
                    u.password = (string)dr["password"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return u;
        }
    }
}


